package Model;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;

import Entity.*;

import java.sql.Connection;
import java.util.ArrayList;

import S2.server.ConnectionManage;

public class MissionModel {

	private static Connection conn = ConnectionManage.getInstance()
			.getConnection();
	
	private ArrayList<String> Customers=new ArrayList<String>();
	
	
	public static Mission addMission(String missionId, String employeeId, String Des,String SDate,ArrayList<String> CustomerId, String EDate ) throws SQLException {

		String sql = "INSERT INTO Mission (missionId,employeeId,Des,SDate,EDate)"+"VALUES(?,?,?,?,?)";
		String sql2;
		ResultSet rs = null;
		Mission bean =  new Mission();
        
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setString(1, missionId);
			stmt.setString(2, employeeId);
			
			stmt.setString(4 , Des);
			stmt.setString(5 , SDate);
			stmt.setString(6 , EDate);
			
			rs = stmt.executeQuery();
			

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} 
			
			
			try  {
			
				for(int i=0; i<CustomerId.size();i++)
				{
					
				
					sql2 = "INSERT INTO MissionCustomer (missionId,CustomerId)"+"VALUES(?,?)";
					PreparedStatement stmt = conn.prepareStatement(sql2);
					stmt.setString(1, missionId);
					stmt.setString(2,CustomerId.get(i));
					rs = stmt.executeQuery();
				}
				

				
				

			} catch (SQLException e) {
				System.err.println(e);
				return null;
			} finally {
				if (rs != null) {
					rs.close();
				}
		}
		return bean;
		
	}
	public static Mission UpdateMission(String missionId, String employeeId, String Des,String SDate,ArrayList<String> CustomerId, String EDate ) throws SQLException {
		String sql = "Update INTO Mission (missionId,employeeId,Des,SDate,EDate)"+"VALUES(?,?,?,?,?)";
		String sql2;
		ResultSet rs = null;
		Mission bean =  new Mission();
		
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setString(1, missionId);
			stmt.setString(2, employeeId);
			
			stmt.setString(4 , Des);
			stmt.setString(5 , SDate);
			stmt.setString(6 , EDate);
			
			rs = stmt.executeQuery();
			

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} 
		
		try  {
			
			for(int i=0; i<CustomerId.size();i++)
			{
				
			
				sql2 = "Update INTO MissionCustomer (missionId,CustomerId)"+"VALUES(?,?)";
				PreparedStatement stmt = conn.prepareStatement(sql2);
				stmt.setString(1, missionId);
				stmt.setString(2,CustomerId.get(i));
				rs = stmt.executeQuery();
			}
			

			
			

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
	}
	return bean;
			
	}
	public static void DeleteMission(String missionId, String employeeId, String Des,String SDate,ArrayList<String> CustomerId, String EDate ) throws SQLException {
		String sql = "Delete FROM Mission " +  "WHERE missionId = ?";
		
		ResultSet rs = null;
		
		try (PreparedStatement stmt = conn.prepareStatement(sql);)
		{
			stmt.setString(1, missionId);	
			rs = stmt.executeQuery();
		}
		 catch (SQLException e) {
				System.err.println(e);
				
			}
		String sql2="Delete FROM MissionCustomer" + "WHERE missionId=?";
		try (PreparedStatement stmt = conn.prepareStatement(sql2);)
		{	
			stmt.setString(1, missionId);
			rs = stmt.executeQuery();
		}
		 catch (SQLException e) {
				System.err.println(e);
				
			}
		
		
		finally {
				if (rs != null) {
					rs.close();
				}
	}
}
	public static Mission GetMission(String missionId) throws SQLException {
		String sql = "SELECT M.*,MC.* FROM" + " Mission M,MissionCustomer MC WHERE" + " M.missionId=M.missionId AND MC.missionId=? ";
		ResultSet rs = null;
		Mission bean =  new Mission();

		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setString(1, missionId);
		
		
			rs = stmt.executeQuery();
			
			if (rs.next()) {
				bean.setMissionId(missionId);
				bean.setEmployeeId(missionId); //bean.setRole(rs.getInt("role"));
				bean.setDes(rs.getString("employeeId"));
				bean.setSDate(rs.getDate("SDate"));
				bean.setEDate(rs.getDate("EDate"));
				//for(int i=0;i<Customers.size();)
			//	bean.setCustomers(rs.getString("EDate"));
					
			
				return bean;
			} else {
				return null;
			}

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
	}
		
	}
